local Db = {}
local _VERSION = '1.0.6'
Db._VERSION = _VERSION

-- 设置Lua模块搜索路径
package.cpath = package.cpath .. ';/usr/lib/lua/?.so;/usr/lib/lua/luasql/?.so'

local config = require("config.database")
local json = require("cjson")

-- SQLite3驱动特殊处理
local function load_sqlite()
    local ok, sqlite = pcall(require, "luasql.sqlite3")
    if not ok then return nil, sqlite end
    
    if type(sqlite) == "table" then
        if sqlite.sqlite3 then
            return sqlite.sqlite3()  -- 标准格式
        else
            return sqlite()  -- 备用格式
        end
    end
    return nil, "Invalid SQLite driver format"
end

-- 连接池管理
local connection_pools = {}

-- 获取当前时间戳(毫秒)
local function get_current_timestamp()
    return os.time() * 1000
end

-- 转义字符串
local function escape_value(conn, value)
    if value == nil then
        return "NULL"
    elseif type(value) == "string" then
        return "'" .. (conn.escape and conn:escape(value) or value:gsub("'", "''")) .. "'"
    elseif type(value) == "boolean" then
        return value and "1" or "0"
    elseif type(value) == "table" then
        return "'" .. (conn.escape and conn:escape(json.encode(value)) or json.encode(value):gsub("'", "''")) .. "'"
    else
        return tostring(value)
    end
end

-- 创建数据库连接
local function create_connection(db_config)
    local db_type = db_config.type or "sqlite3"
    
    if db_type == "sqlite3" then
        local env, err = load_sqlite()
        if not env then error("SQLite init failed: " .. err) end
        
        local conn = env:connect(db_config.database)
        if not conn then error("SQLite connect failed") end
        return conn
    else
        error("Only SQLite3 is supported in this version")
    end
end

-- 从连接池获取连接
local function get_connection(connection_name)
    connection_name = connection_name or "default"
    local db_config = config.connections[connection_name] or config.connections[config.default]
    
    if not db_config then
        error("Database configuration not found for: " .. connection_name)
    end
    
    -- 设置默认连接池配置
    db_config.pool = db_config.pool or {
        max = 5,
        timeout = 5000,
        idle_timeout = 30000
    }
    
    if not connection_pools[connection_name] then
        connection_pools[connection_name] = {
            config = db_config,
            connections = {},
            last_used = {}
        }
    end
    
    local pool = connection_pools[connection_name]
    local now = get_current_timestamp()
    
    -- 查找可用连接
    for i, conn in ipairs(pool.connections) do
        if not conn.in_use and (now - pool.last_used[i]) < db_config.pool.idle_timeout then
            conn.in_use = true
            pool.last_used[i] = now
            return conn.conn, function()
                conn.in_use = false
                pool.last_used[i] = get_current_timestamp()
            end
        end
    end
    
    -- 创建新连接
    if #pool.connections < db_config.pool.max then
        local conn = create_connection(db_config)
        local conn_wrapper = {
            conn = conn,
            in_use = true
        }
        table.insert(pool.connections, conn_wrapper)
        pool.last_used[#pool.connections] = now
        return conn, function()
            conn_wrapper.in_use = false
            pool.last_used[#pool.connections] = get_current_timestamp()
        end
    end
    
    -- 等待可用连接
    local start_time = get_current_timestamp()
    while (get_current_timestamp() - start_time) < db_config.pool.timeout do
        for i, conn in ipairs(pool.connections) do
            if not conn.in_use then
                conn.in_use = true
                pool.last_used[i] = get_current_timestamp()
                return conn.conn, function()
                    conn.in_use = false
                    pool.last_used[i] = get_current_timestamp()
                end
            end
        end
    end
    
    error("Get database connection timeout")
end

--- 获取配置
-- @param connection_name 连接名(可选)
-- @return 配置表
function Db.getConfig(connection_name)
    connection_name = connection_name or "default"
    local pool = connection_pools[connection_name]
    if not pool then
        error("Database connection not initialized: " .. connection_name)
    end
    return pool.config
end

--- 设置配置
-- @param config 新配置
-- @param connection_name 连接名(可选)
function Db.setConfig(config, connection_name)
    connection_name = connection_name or "default"
    if not connection_pools[connection_name] then
        connection_pools[connection_name] = {
            config = config,
            connections = {},
            last_used = {}
        }
    else
        connection_pools[connection_name].config = config
    end
end

--- 使用表前缀创建模型(类似Db::name())
-- @param table_name 不带前缀的表名
-- @param connection_name 连接名(可选)
-- @return 模型实例
function Db.name(table_name, connection_name)
    local config = Db.getConfig(connection_name)
    local full_table_name = config.prefix .. table_name
    return Db.Model(full_table_name, connection_name)
end

--- 使用完整表名创建模型(类似Db::table())
-- @param table_name 完整表名(带前缀)
-- @param connection_name 连接名(可选)
-- @return 模型实例
function Db.table(table_name, connection_name)
    return Db.Model(table_name, connection_name)
end

-- 基础模型类
local Model = {}
Model.__index = Model

--- 创建新模型实例
-- @param table_name 表名
-- @param connection_name 连接名(可选)
-- @return 模型实例
function Db.Model(table_name, connection_name)
    local instance = {
        _table = table_name,
        _connection = connection_name,
        _fields = "*",
        _where = {},
        _join = {},
        _order = nil,
        _group = nil,
        _having = nil,
        _limit = nil,
        _offset = nil,
        _data = {},
        _alias = nil,
        _incr = {},
        _decr = {},
        _options = {}
    }
    return setmetatable(instance, Model)
end

--- 设置表别名
-- @param alias 别名
-- @return 模型实例
function Model:alias(alias)
    self._alias = alias
    return self
end

--- 设置查询字段
-- @param fields 字段列表或字符串
-- @return 模型实例
function Model:field(fields)
    if type(fields) == "table" then
        self._fields = table.concat(fields, ",")
    else
        self._fields = fields
    end
    return self
end

--- 设置WHERE条件
-- @param field 字段名或条件表
-- @param op 操作符或值(可选)
-- @param value 值(可选)
-- @return 模型实例
function Model:where(field, op, value)
    if type(field) == "table" then
        for k, v in pairs(field) do
            if type(k) == "number" then
                table.insert(self._where, v)
            else
                table.insert(self._where, {k, "=", v})
            end
        end
    else
        if value == nil then
            value = op
            op = "="
        end
        table.insert(self._where, {field, op, value})
    end
    return self
end

--- 设置OR WHERE条件
-- @param field 字段名或条件表
-- @param op 操作符或值(可选)
-- @param value 值(可选)
-- @return 模型实例
function Model:whereOr(field, op, value)
    if type(field) == "table" then
        local or_conditions = {}
        for k, v in pairs(field) do
            if type(k) == "number" then
                table.insert(or_conditions, v)
            else
                table.insert(or_conditions, {k, "=", v})
            end
        end
        table.insert(self._where, {or_conditions = or_conditions})
    else
        if value == nil then
            value = op
            op = "="
        end
        table.insert(self._where, {or_conditions = {{field, op, value}}})
    end
    return self
end

--- 设置JOIN条件
-- @param table 表名
-- @param condition JOIN条件
-- @param type JOIN类型(INNER, LEFT, RIGHT)(可选)
-- @return 模型实例
function Model:join(table, condition, type)
    type = type or "INNER"
    table.insert(self._join, {
        table = table,
        condition = condition,
        type = type
    })
    return self
end

--- 设置ORDER条件
-- @param order 排序条件
-- @return 模型实例
function Model:order(order)
    self._order = order
    return self
end

--- 设置GROUP条件
-- @param group 分组字段
-- @return 模型实例
function Model:group(group)
    self._group = group
    return self
end

--- 设置HAVING条件
-- @param having HAVING条件
-- @return 模型实例
function Model:having(having)
    self._having = having
    return self
end

--- 设置LIMIT条件
-- @param limit 限制数量
-- @param offset 偏移量(可选)
-- @return 模型实例
function Model:limit(limit, offset)
    self._limit = limit
    if offset then
        self._offset = offset
    end
    return self
end

--- 设置数据
-- @param data 数据表
-- @return 模型实例
function Model:data(data)
    self._data = data
    return self
end

--- 设置字段自增
-- @param field 字段名
-- @param step 步长(可选，默认为1)
-- @return 模型实例
function Model:incr(field, step)
    self._incr[field] = step or 1
    return self
end

--- 设置字段自减
-- @param field 字段名
-- @param step 步长(可选，默认为1)
-- @return 模型实例
function Model:decr(field, step)
    self._decr[field] = step or 1
    return self
end

--- 设置查询选项
-- @param options 选项表
-- @return 模型实例
function Model:options(options)
    self._options = options
    return self
end

-- 构建WHERE条件SQL
local function build_where(conn, where)
    local conditions = {}
    
    for _, condition in ipairs(where) do
        if condition.or_conditions then
            local or_conditions = {}
            for _, or_cond in ipairs(condition.or_conditions) do
                if type(or_cond) == "table" and #or_cond == 3 then
                    table.insert(or_conditions, or_cond[1] .. or_cond[2] .. escape_value(conn, or_cond[3]))
                else
                    table.insert(or_conditions, tostring(or_cond))
                end
            end
            table.insert(conditions, "(" .. table.concat(or_conditions, " OR ") .. ")")
        else
            if type(condition) == "table" and #condition == 3 then
                table.insert(conditions, condition[1] .. condition[2] .. escape_value(conn, condition[3]))
            else
                table.insert(conditions, tostring(condition))
            end
        end
    end
    
    return #conditions > 0 and " WHERE " .. table.concat(conditions, " AND ") or ""
end

-- 构建JOIN条件SQL
local function build_join(joins)
    local join_sql = {}
    
    for _, join in ipairs(joins) do
        table.insert(join_sql, string.format("%s JOIN %s ON %s", join.type, join.table, join.condition))
    end
    
    return #join_sql > 0 and " " .. table.concat(join_sql, " ") or ""
end

--- 执行查询
-- @return 结果集
function Model:select()
    local conn, release = get_connection(self._connection)
    local db_config = connection_pools[self._connection or "default"].config
    
    -- 构建表名
    local table_name = self._table
    if self._alias then
        table_name = table_name .. " AS " .. self._alias
    end
    
    -- 构建SQL
    local sql = string.format("SELECT %s FROM %s%s%s",
        self._fields,
        table_name,
        build_join(self._join),
        build_where(conn, self._where))
    
    -- 添加GROUP BY
    if self._group then
        sql = sql .. " GROUP BY " .. self._group
    end
    
    -- 添加HAVING
    if self._having then
        sql = sql .. " HAVING " .. self._having
    end
    
    -- 添加ORDER BY
    if self._order then
        sql = sql .. " ORDER BY " .. self._order
    end
    
    -- 添加LIMIT
    if self._limit then
        sql = sql .. " LIMIT " .. self._limit
        if self._offset then
            sql = sql .. " OFFSET " .. self._offset
        end
    end
    
    -- 执行查询
    local cursor, err = conn:execute(sql)
    if not cursor then
        release()
        error("SQL error: " .. (err or "unknown") .. "\nSQL: " .. sql)
    end
    
    -- 获取结果
    local result = {}
    local row = cursor:fetch({}, "a")
    while row do
        table.insert(result, row)
        row = cursor:fetch(row, "a")
    end
    
    cursor:close()
    release()
    return result
end

--- 查询单条记录
-- @return 单条记录
function Model:find()
    self:limit(1)
    local result = self:select()
    return result[1]
end

--- 插入数据
-- @param data 数据表(可选)
-- @return 插入ID或结果
function Model:insert(data)
    data = data or self._data
    if not data or type(data) ~= "table" or not next(data) then
        error("No data to insert")
    end
    
    local conn, release = get_connection(self._connection)
    local db_config = connection_pools[self._connection or "default"].config
    
    -- 构建字段和值
    local fields = {}
    local values = {}
    
    for k, v in pairs(data) do
        table.insert(fields, k)
        table.insert(values, escape_value(conn, v))
    end
    
    -- 构建SQL
    local sql = string.format("INSERT INTO %s (%s) VALUES (%s)",
        self._table,
        table.concat(fields, ","),
        table.concat(values, ","))
    
    -- 执行插入
    local result, err = conn:execute(sql)
    if not result then
        release()
        error("SQL error: " .. (err or "unknown") .. "\nSQL: " .. sql)
    end
    
    -- 获取插入ID
    local last_insert_id
    if db_config.type == "sqlite3" then
        local cursor = conn:execute("SELECT last_insert_rowid() AS id")
        if cursor then
            local row = cursor:fetch({}, "a")
            last_insert_id = row and row.id
            cursor:close()
        end
    end
    
    release()
    return last_insert_id or result
end

--- 更新数据
-- @param data 数据表(可选)
-- @return 影响的行数
function Model:update(data)
    data = data or self._data
    
    local conn, release = get_connection(self._connection)
    local db_config = connection_pools[self._connection or "default"].config
    
    -- 构建SET部分
    local set_parts = {}
    
    -- 普通字段更新
    if data and type(data) == "table" then
        for k, v in pairs(data) do
            table.insert(set_parts, k .. "=" .. escape_value(conn, v))
        end
    end
    
    -- 自增字段
    for field, step in pairs(self._incr) do
        table.insert(set_parts, field .. "=" .. field .. "+" .. step)
    end
    
    -- 自减字段
    for field, step in pairs(self._decr) do
        table.insert(set_parts, field .. "=" .. field .. "-" .. step)
    end
    
    if #set_parts == 0 then
        release()
        error("No data to update")
    end
    
    -- 构建SQL
    local sql = string.format("UPDATE %s SET %s%s",
        self._table,
        table.concat(set_parts, ","),
        build_where(conn, self._where))
    
    -- 执行更新
    local result, err = conn:execute(sql)
    if not result then
        release()
        error("SQL error: " .. (err or "unknown") .. "\nSQL: " .. sql)
    end
    
    release()
    return result
end

--- 删除数据
-- @return 影响的行数
function Model:delete()
    local conn, release = get_connection(self._connection)
    local db_config = connection_pools[self._connection or "default"].config
    
    -- 构建SQL
    local sql = string.format("DELETE FROM %s%s",
        self._table,
        build_where(conn, self._where))
    
    -- 执行删除
    local result, err = conn:execute(sql)
    if not result then
        release()
        error("SQL error: " .. (err or "unknown") .. "\nSQL: " .. sql)
    end
    
    release()
    return result
end

--- 执行原生查询
-- @param sql SQL语句
-- @param params 参数表(可选)
-- @param connection_name 连接名(可选)
-- @return 结果集
function Db.query(sql, params, connection_name)
    local conn, release = get_connection(connection_name)
    
    -- 替换参数
    if params and type(params) == "table" then
        for k, v in pairs(params) do
            sql = sql:gsub(":" .. k, escape_value(conn, v))
        end
    end
    
    -- 执行查询
    local cursor, err = conn:execute(sql)
    if not cursor then
        release()
        error("SQL error: " .. (err or "unknown") .. "\nSQL: " .. sql)
    end
    
    -- 获取结果
    local result = {}
    local row = cursor:fetch({}, "a")
    while row do
        table.insert(result, row)
        row = cursor:fetch(row, "a")
    end
    
    cursor:close()
    release()
    return result
end

--- 执行原生写入操作
-- @param sql SQL语句
-- @param params 参数表(可选)
-- @param connection_name 连接名(可选)
-- @return 执行结果
function Db.execute(sql, params, connection_name)
    local conn, release = get_connection(connection_name)
    
    -- 替换参数
    if params and type(params) == "table" then
        for k, v in pairs(params) do
            sql = sql:gsub(":" .. k, escape_value(conn, v))
        end
    end
    
    -- 执行SQL
    local result, err = conn:execute(sql)
    if not result then
        release()
        error("SQL error: " .. (err or "unknown") .. "\nSQL: " .. sql)
    end
    
    release()
    return result
end

--- 开启事务
-- @param connection_name 连接名(可选)
-- @return 成功返回true
function Db.startTrans(connection_name)
    local conn, release = get_connection(connection_name)
    local result, err = conn:execute("BEGIN")
    if not result then
        release()
        error("Start transaction failed: " .. (err or "unknown"))
    end
    
    -- 保存连接不释放
    return true
end

--- 提交事务
-- @param connection_name 连接名(可选)
-- @return 成功返回true
function Db.commit(connection_name)
    local pool = connection_pools[connection_name or "default"]
    if not pool then
        error("No active transaction")
    end
    
    for i, conn in ipairs(pool.connections) do
        if conn.in_use then
            local result, err = conn.conn:execute("COMMIT")
            conn.in_use = false
            pool.last_used[i] = get_current_timestamp()
            if not result then
                error("Commit failed: " .. (err or "unknown"))
            end
            return true
        end
    end
    
    error("No active transaction")
end

--- 回滚事务
-- @param connection_name 连接名(可选)
-- @return 成功返回true
function Db.rollback(connection_name)
    local pool = connection_pools[connection_name or "default"]
    if not pool then
        error("No active transaction")
    end
    
    for i, conn in ipairs(pool.connections) do
        if conn.in_use then
            local result, err = conn.conn:execute("ROLLBACK")
            conn.in_use = false
            pool.last_used[i] = get_current_timestamp()
            if not result then
                error("Rollback failed: " .. (err or "unknown"))
            end
            return true
        end
    end
    
    error("No active transaction")
end

return Db